﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using WMSSyncData.Model;
using WMSSyncData.Model.WMSModel;
using WMSSyncData.Utils;

namespace WMSSyncData.DAL.SqlDB
{
    public class Tddev106DAL : BaseSqlDBDAL<tddev106>
    {

        /// <summary>
        /// 生产退料
        /// </summary>
        /// <returns></returns>
        public List<tddev106> GetSyncData()
        {
            //var sql = @"SELECT NEWID() AS guid,temp.BillNo as WMSNO,temp.RelatedBill as orno,temp.RowNo as pono,
            //            '2' as otyp,ISNULL(TEMP.Ext1,' ') as sfco,ISNULL(TEMP.Ext2,' ') as sflo,PartNumber as item, ISNULL(Storage_SN,' ') 
            //            as stco,' ' as stlo,temp.Qty as qoor,1 as host,' 'as mess,getdate() as edte1,
            //            0 AS refcntu,0 AS refcntd,getdate() as wdte1,getdate() as edte2 FROM ( SELECT
            //        a.BillNo, a.RelatedBill,b.RowNo,   a.Storage_SN,c.PartNumber,SUM(c.RealQty) AS Qty,a.Ext1,a.Ext2 FROM dbo.wms_T_InStockRM a
            //            inner JOIN dbo.wms_T_InStockRMRows b ON a.BillNo=b.BillNo
            //            inner JOIN dbo.wms_T_InStockRMRowDetails c ON b.BillNo=c.BillNo and b.RowNo=c.RowNo
            //            WHERE a.Status='3' and BillType='退料入库' GROUP BY a.BillNo,a.RelatedBill,b.RowNo,a.Storage_SN,c.PartNumber,a.Ext1,a.Ext2 ) temp";
            var sql = @"SELECT NEWID() AS guid,temp.BillNo as WMSNO,' ' as orno,temp.RowNo as pono,
                        'TWH' as otyp, case when ISNULL(temp.Ext1,' ')=' ' then ' ' else ISNULL(temp.Ext1,' ') end as sfco,' ' as sflo,PartNumber as item, ISNULL(Storage_SN,' ') 
                        as stco,' ' as stlo,temp.Qty as qoor,1 as host,' 'as mess,getdate() as edte1,
                        0 AS refcntu,0 AS refcntd,getdate() as wdte1,getdate() as edte2,'WH5' as seri,temp.Version as revi,' ' as Remark1,' ' as Remark2,' ' as Remark3,' ' as Remark4,' ' as Remark5 FROM ( SELECT
                    a.BillNo, a.RelatedBill,b.RowNo,   a.Storage_SN,b.PartNumber,b.RealQty AS Qty,b.Ext1,b.Ext2,b.Version FROM dbo.wms_T_InStockRM a
                        inner JOIN dbo.wms_T_InStockRMRows b ON a.BillNo=b.BillNo
                        WHERE a.Status='6' and BillType='退料入库'  ) temp";
            var dt = SqlDBHelper.GetDataSet(sql).Tables[0];
            var result = MakeTablePackage(dt);
            return result;
        }

        public DataTable GetSyncTLRKData()
        {
            var sql = @"SELECT  a.BillNo, a.RelatedBill,b.RowNo,   a.Storage_SN,b.PartNumber,b.RealQty AS Qty,b.Ext1,b.Ext2,Upper(b.Version) as Version,c.Storage_SN as FGSN,c.FGQty,c.Ext2 as NGSN,c.NGQty FROM dbo.wms_T_InStockRM a
                        inner JOIN dbo.wms_T_InStockRMRows b ON a.BillNo=b.BillNo
						inner join wms_T_IQC c on a.BillNo=c.BillNo AND c.RowNo = b.RowNo
                        WHERE a.ChkStatus='4' and (BillType='退料入库' or BillType='非拉动退料入库')";
            var dt = SqlDBHelper.GetDataSet(sql).Tables[0];
            return dt;
        }


        public DataTable GetSyncRKData()
        {
            var sql = @"SELECT  a.BillNo, a.RelatedBill,b.RowNo,   a.Storage_SN,b.PartNumber,b.RealQty AS Qty,b.Ext1,b.Ext2,b.Version,c.Storage_SN as FGSN,c.FGQty,c.Ext2 as NGSN,c.NGQty FROM dbo.wms_T_InStockRM a
                        inner JOIN dbo.wms_T_InStockRMRows b ON a.BillNo=b.BillNo
						inner join wms_T_IQC c on a.BillNo=c.BillNo AND b.RowNo=c.RowNo
                        WHERE a.Status='3' or a.ChkStatus='3'";
            /*
            var sql = @"SELECT  a.BillNo, a.RelatedBill,b.RowNo,   a.Storage_SN,b.PartNumber,b.RealQty AS Qty,b.Version,c.Storage_SN as FGSN,c.FGQty,c.Ext1,c.Ext2 as NGSN,c.Ext3,c.NGQty FROM dbo.wms_T_InStockRM a
                        inner JOIN dbo.wms_T_InStockRMRows b ON a.BillNo=b.BillNo
						inner join wms_T_IQC c on a.BillNo=c.BillNo
                        WHERE a.Status='3' ";
                        */
            var dt = SqlDBHelper.GetDataSet(sql).Tables[0];
            return dt;
        }

        public bool UpdateUploadStatus(List<WMS_T_InstockRMRowDetails> list)
        {
            var result = false;
            var billNoList = list.Select(x => x.BillNo).Distinct();
            if (billNoList.Count() > 0)
            {
                var sqlWhere = "where BillNo in (";
                foreach (var item in billNoList)
                {
                    sqlWhere = sqlWhere + "'" + item + "',";
                }
                sqlWhere = sqlWhere.TrimEnd(',');
                sqlWhere = sqlWhere + ")";
                var sql = "UPDATE  wms_T_InStockRM SET Status='9'" + sqlWhere;
                var updateRes = SqlDBHelper.ExcuteSQL(sql);
                if (updateRes > 0)
                {
                    result = true;
                }
            }
            return result;

        }

        //public bool UpdateUploadFGNGStatus(DataTable dt)
        //{
        //    var result = false;
        //    //var billNoList = list.Select(x => x.BillNo).Distinct();

        //    var sqlWhere = "where BillNo in (";
        //    foreach (DataRow item in dt.Rows)
        //    {
        //        sqlWhere = sqlWhere + "'" + item["BillNo"].ToString() + "',";
        //    }
        //    sqlWhere = sqlWhere.TrimEnd(',');
        //    sqlWhere = sqlWhere + ")";
        //    var sql = "UPDATE  wms_T_InStockRM SET Status='9'" + sqlWhere;
        //    var updateRes = SqlDBHelper.ExcuteSQL(sql);
        //    if (updateRes > 0)
        //    {
        //        result = true;
        //    }

        //    return result;

        //}

        public bool CloseBackRMStatus(DataTable dt)
        {
            var result = false;
            //var billNoList = list.Select(x => x.BillNo).Distinct();

            var sqlWhere = "where ChkStatus='4' and BillNo in (";
            foreach (DataRow item in dt.Rows)
            {
                sqlWhere = sqlWhere + "'" + item["BillNo"].ToString() + "',";
            }
            sqlWhere = sqlWhere.TrimEnd(',');
            sqlWhere = sqlWhere + ")";
            var sql = "UPDATE  wms_T_InStockRM SET ChkStatus='6'" + sqlWhere;
            var updateRes = SqlDBHelper.ExcuteSQL(sql);
            if (updateRes > 0)
            {
                result = true;
            }

            return result;
        }

        public bool UpdateUploadRKStatus(DataTable dt)
        {
            var result = false;
            //var billNoList = list.Select(x => x.BillNo).Distinct();

            var sqlWhere = "where BillNo in (";
            string fgBillNo = "";
            string ngBillNo = "";
            var sqlFG = "";
            var sqlNG = "";
            foreach (DataRow item in dt.Rows)
            {
                if (Convert.ToDecimal(item["FGQty"]) == 0)
                {
                    ngBillNo = ngBillNo + "'" + item["BillNo"].ToString() + "',";
                }
                else
                {
                    fgBillNo = fgBillNo + "'" + item["BillNo"].ToString() + "',";
                }
            }
            if (!string.IsNullOrEmpty(fgBillNo))
            {
                fgBillNo = fgBillNo.TrimEnd(',');
                sqlFG = "UPDATE  wms_T_InStockRM SET Status='5'" + sqlWhere + fgBillNo + ");";
            }
            if (!string.IsNullOrEmpty(ngBillNo))
            {
                ngBillNo = ngBillNo.TrimEnd(',');
                sqlNG = "UPDATE  wms_T_InStockRM SET Status='6'" + sqlWhere + ngBillNo + ");";
            }


            var updateRes = SqlDBHelper.ExcuteSQL(sqlFG + sqlNG);
            if (updateRes > 0)
            {
                result = true;
            }

            return result;

        }

        public bool UpdateUploadFTStatus(DataTable dt)
        {
            var result = false;
            //var billNoList = list.Select(x => x.BillNo).Distinct();

            var sqlWhere = "where ChkStatus='3' and BillNo in (";
            string fgBillNo = "";
            string ngBillNo = "";
            var sqlFG = "";
            var sqlNG = "";
            foreach (DataRow item in dt.Rows)
            {
                if (Convert.ToDecimal(item["FGQty"]) == 0)
                {
                    ngBillNo = ngBillNo + "'" + item["BillNo"].ToString() + "',";
                }
                else
                {
                    fgBillNo = fgBillNo + "'" + item["BillNo"].ToString() + "',";
                }
            }
            if (!string.IsNullOrEmpty(fgBillNo))
            {
                fgBillNo = fgBillNo.TrimEnd(',');
                sqlFG = "UPDATE  wms_T_InStockRM SET ChkStatus='4'" + sqlWhere + fgBillNo + ");";
            }
            if (!string.IsNullOrEmpty(ngBillNo))
            {
                ngBillNo = ngBillNo.TrimEnd(',');
                sqlNG = "UPDATE  wms_T_InStockRM SET ChkStatus='6'" + sqlWhere + ngBillNo + ");";
            }

            var updateRes = SqlDBHelper.ExcuteSQL(sqlFG + sqlNG);
            if (updateRes > 0)
            {
                result = true;
            }

            return result;

        }

        /// <summary>
        /// 修改需要同步的POM数据
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public bool UpdateUploadPOMOStatus()
        {
            var result = false;

            var sql = "UPDATE  wms_T_OutStockRMRowDetails SET Status='8' where Status='0' and billno in (select billno from wms_T_OutStockRM where (billtype='领料出库' or billtype='非拉动领料出库'))";
            //var sql = "UPDATE  wms_T_OutStockRMRowDetails SET Status='8' where Status='0' ";
            var updateRes = SqlDBHelper.ExcuteSQL(sql);
            if (updateRes > 0)
            {
                result = true;
            }
            return result;

        }

        public List<tddev106> GetPOMSyncData()
        {
            var sql = @"SELECT NEWID() AS guid,temp.BillNo as WMSNO,' ' as orno,temp.RowNo as pono,
                        'TWH' as otyp,ISNULL(Storage_SN,' ') as sfco,' ' as sflo,PartNumber as item,  
                    case when ISNULL(temp.Ext1,' ')=' ' then ' ' else ISNULL(temp.Ext1,' ') end  as stco,' ' as stlo,temp.Qty as qoor,1 as host,' 'as mess,getdate() as edte1,
                        0 AS refcntu,0 AS refcntd,getdate() as wdte1,getdate() as edte2 ,'WH5' as seri,ISNULL(temp.Version,' ') as revi,' ' as Remark1,' ' as Remark2,' ' as Remark3,' ' as Remark4,' ' as Remark5,0 as epon,0 as seqn
                    FROM ( SELECT
                        a.BillNo, a.RelatedBill,b.RowNo,   c.Storage_SN,c.PartNumber,SUM(c.RealQty) AS Qty,Upper(c.Version) as Version,
                        c.Location_SN,b.Ext1,b.Ext2 FROM dbo.wms_T_OutStockRM a
                        inner JOIN dbo.wms_T_OutStockRMRows b ON a.BillNo=b.BillNo
                        inner JOIN dbo.wms_T_OutStockRMRowDetails c ON b.BillNo=c.BillNo and b.RowNo=c.RowNo and b.OrderNo=c.OrderNo 
                        WHERE c.Status='8' and (a.billtype='领料出库' or BillType='非拉动领料出库')  GROUP BY a.BillNo,a.RelatedBill,b.RowNo,c.Storage_SN,c.PartNumber,c.Location_SN,b.Ext1,b.Ext2,c.Version ) temp";

            var dt = SqlDBHelper.GetDataSet(sql).Tables[0];
            var result = MakeTablePackage(dt);
            return result;
        }

        public List<tddev106> GetPBackSyncData()
        {
            var sql = @"SELECT NEWID() AS guid,temp.BillNo as WMSNO,' ' as orno,temp.RowNo as pono,
                        'TWH' as otyp, isnull(temp.Ext1,' ') as sfco,' '  as sflo,PartNumber as item,  
                    ISNULL(Storage_SN,' ') as stco,' ' as stlo,temp.Qty as qoor,1 as host,' 'as mess,getdate() as edte1,
                        0 AS refcntu,0 AS refcntd,getdate() as wdte1,getdate() as edte2  ,'WH5' as seri,temp.Version as revi,' ' as Remark1,' ' as Remark2,' ' as Remark3,' ' as Remark4,' ' as Remark5,0 as epon,0 as seqn  
                    FROM ( SELECT
                        a.BillNo, a.RelatedBill,b.RowNo,   c.Storage_SN,c.PartNumber,SUM(c.RealQty) AS Qty,
                        c.Location_SN,a.Ext1,a.Ext2,Upper(c.Version) as Version FROM dbo.wms_T_OutStockRM a
                        inner JOIN dbo.wms_T_OutStockRMRows b ON a.BillNo=b.BillNo
                        inner JOIN dbo.wms_T_OutReturn c ON b.BillNo=c.BillNo and b.RowNo=c.RowNo and b.OrderNo=c.OrderNo
                        WHERE c.Status='0'  GROUP BY a.BillNo,a.RelatedBill,b.RowNo,c.Storage_SN,c.PartNumber,c.Location_SN,a.Ext1,a.Ext2,c.Version ) temp ";

            var dt = SqlDBHelper.GetDataSet(sql).Tables[0];
            var result = MakeTablePackage(dt);
            return result;
        }


        /// <summary>
        /// 修改需要同步的POM数据
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public bool UpdateUploadPOMTStatus(List<WMS_T_OutStockRMRowDetails> list)
        {
            var result = false;
            //int dataOrderNo = Convert.ToInt32(DateTime.Now.Year.ToString().Substring(2, 2) + DateTime.Now.ToString("MMddHHmm"));
            var billNoList = list.Select(x => x.BillNo).Distinct();
            if (billNoList.Count() > 0)
            {
                string sqlwhere = "";
                foreach (string billno in billNoList)
                {
                    sqlwhere = sqlwhere + "'" + billno + "',";
                }
                sqlwhere = sqlwhere.TrimEnd(',');
                var sql = "UPDATE  wms_T_OutStockRMRowDetails SET Status='9'  where Status='8' and BillNo in (" + sqlwhere + "); ";
                var updateRes = SqlDBHelper.ExcuteSQL(sql);
                if (updateRes > 0)
                {
                    result = true;
                }
            }
            return result;

        }


        public bool UpdateUploadPBackStatus(DataTable dt)
        {
            var result = false;

            if (dt != null && dt.Rows.Count > 0)
            {
                var sqlWhere = "where UniqueCode in (";
                foreach (DataRow item in dt.Rows)
                {
                    sqlWhere = sqlWhere + "'" + item["UniqueCode"] + "',";
                }
                sqlWhere = sqlWhere.TrimEnd(',');
                sqlWhere = sqlWhere + ") and Status='0'";
                var sql = "UPDATE  wms_T_OutReturn SET Status='9'" + sqlWhere;
                var updateRes = SqlDBHelper.ExcuteSQL(sql);
                if (updateRes > 0)
                {
                    result = true;
                }
            }
            return result;

        }



        public List<tddev106> GetTransferSyncData()
        {
            var sql = @"select T.guid,T.BillNo as WMSNO,' ' as orno,0 as pono,'TWH' as otyp,ISNULL(T.OutStorage_SN,' ') as sfco,' ' as sflo, 
                        T.PartNumber as item,isnull(T.InStorage_SN,' ')  as stco,' ' as stlo,ih.Qty as qoor,1 as host,
                        ' 'as mess,getdate() as edte1,  0 AS refcntu,0 AS refcntd,getdate() as wdte1,getdate() as edte2, isnull(T.Ext1,' ') as seri,
                        (case when T.Version='' then ' ' when T.Version is null then ' ' else upper(T.Version) end) as revi,
                        ' ' as Remark1,' ' as Remark2,' ' as Remark3,' ' as Remark4,' ' as Remark5,0 as epon,0 as seqn
                        from wms_T_Transfer T inner join wms_T_InventoryHistory ih ON T.BillNo=ih.Location_SN and ih.IO='I' where status='2' and BillType = '移库单' 
                        and isnull(OutStorage_SN,'')<>ISNULL(InStorage_SN,'')
                        UNION                        
                        select T.guid,T.BillNo as WMSNO,' ' as orno,0 as pono,'TWH' as otyp,ISNULL(T.OutStorage_SN,' ') as sfco,' ' as sflo, 
                        T.PartNumber as item,isnull(T.InStorage_SN,' ')  as stco,' ' as stlo,Qty as qoor,1 as host,
                        ' 'as mess,getdate() as edte1,  0 AS refcntu,0 AS refcntd,getdate() as wdte1,getdate() as edte2, 'WH5' as seri,
                        (case when T.Version='' then ' ' when T.Version is null then ' ' else upper(T.Version) end) as revi,
                        ' ' as Remark1,' ' as Remark2,' ' as Remark3,' ' as Remark4,' ' as Remark5,0 as epon,0 as seqn
                        from wms_T_Transfer T  where status='2' and (BillType = '外发单' or BillType = '批量外发单')
                        and isnull(OutStorage_SN,'')<>ISNULL(InStorage_SN,'')";

            var dt = SqlDBHelper.GetDataSet(sql).Tables[0];
            var result = MakeTablePackage(dt);
            return result;
        }

        public DataTable GetOutStatuOverSyncData()
        {
            var sql = @" select BillNo,RowNo from wms_T_OutStockRMRows where [Status]='1'  and billno in (select billno from wms_T_OutStockRM where billtype='领料出库') ";

            var dt = SqlDBHelper.GetDataSet(sql).Tables[0];

            return dt;
        }

        /// <summary>
        /// 修改需要同步的POM数据
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public bool UpdateTransferStatus(List<tddev106> list)
        {
            var result = false;

            if (list.Count() > 0)
            {
                var sqlWhere = "where guid in (";
                foreach (var item in list)
                {
                    sqlWhere = sqlWhere + "'" + item.guid + "',";
                }
                sqlWhere = sqlWhere.TrimEnd(',');
                sqlWhere = sqlWhere + ") ";
                var sql = "UPDATE  wms_T_Transfer SET Status='9' " + sqlWhere;
                var updateRes = SqlDBHelper.ExcuteSQL(sql);
                if (updateRes > 0)
                {
                    result = true;
                }
            }
            return result;

        }

        public int UpdateOutStatusOver(DataTable dt)
        {
            StringBuilder sb = new StringBuilder();

            sb.AppendLine("begin");
            foreach (DataRow item in dt.Rows)
            {
                var sql = "UPDATE wms_T_OutStockRMRows set  [Status]='9' where BillNo='" + item["BillNo"].ToString() + "' and rowNo='" + item["RowNo"].ToString() + "';";
                sb.AppendLine(sql);
            }
            sb.AppendLine("end;");

            var resultSql = sb.ToString().Trim();
            var result = SqlDBHelper.ExcuteSQL(resultSql);
            return result;
        }

        public int CloseYwHead()
        {
            var sql = @"Update wms_T_OutStockRM set Status=9,Remark='服务关闭' where BillNo in(
select r.BillNo from wms_T_OutStockRM r inner join(
select BillNo from wms_T_OutStockRMRowDetails GROUP BY BillNo HAVING Min(Status)= 9)
tp on r.BillNo = tp.BillNo where r.Status = 1)";
            var result = SqlDBHelper.ExcuteSQL(sql);
            return result;
        }
    }
}
